package forum.dao.mysql;

import forum.dao.PostDAO;
import forum.dao.util.MySQLUtil;
import forum.entities.Post;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

public class MySQLPostDAO implements PostDAO {
    private static final String CREATE_QUERY =
            "INSERT INTO posts(postContains,authorId,topicId) VALUES(NULL,NULL,NULL)";
    private static final String FIND_POST_BY_ID_QUERY =
            "SELECT * FROM posts WHERE id = ?";
    private static final String UPDATE_QUERY =
            "UPDATE posts SET postContains = ?, authorId = ?, topicId = ? WHERE id = ?";
    private static final String DELETE_QUERY =
            "DELETE FROM posts WHERE id = ?";
    private static final String FIND_POSTS_BY_TOPIC_ID_QUERY =
            "SELECT * FROM posts WHERE topicId = ?";

    private static ConnectionPool connectionPool = null;

    static {
        String createTableQuery = "CREATE TABLE IF NOT EXISTS posts" +
                "(id BIGINT(20) PRIMARY KEY NOT NULL AUTO_INCREMENT, " +
                "postContains VARCHAR(200), " +
                "authorId BIGINT(20), " +
                "topicId BIGINT(20))";
        Connection conn = null;
        PreparedStatement createTableStatement = null;
        try {
            connectionPool = C3P0ConnectionPool.getInstance();
            conn =  connectionPool.getConnection();
            createTableStatement = conn.prepareStatement(createTableQuery);
            createTableStatement.execute();
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(createTableStatement);
            MySQLUtil.close(conn);
        }
    }


    @Override
    public Post create() {
        Post post = null;
        Connection connection = null;
        PreparedStatement updatePostStatement = null;
        ResultSet generatedKey = null;
        try {
            connection = connectionPool.getConnection();
            updatePostStatement = MySQLUtil.prepareStatement(connection, CREATE_QUERY, true);
            updatePostStatement.executeUpdate();
            generatedKey = updatePostStatement.getGeneratedKeys();
            if (!generatedKey.next()) {
                // TODO Create DAOException class and add DAOException to GenericDAO methods signature.
                // throw new DAOException("No generated key obtained");
            }
            post = new Post(BigInteger.valueOf(generatedKey.getLong(1)));
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(generatedKey);
            MySQLUtil.close(updatePostStatement);
            MySQLUtil.close(connection);
        }
        return post;
    }

    @Override
    public Post read(BigInteger id) {
        Post post = null;
        Connection connection = null;
        PreparedStatement findPostByIdStatement = null;
        ResultSet rs = null;
        try {
            connection = connectionPool.getConnection();
            findPostByIdStatement = MySQLUtil.prepareStatement(connection, FIND_POST_BY_ID_QUERY, false, id);
            rs = findPostByIdStatement.executeQuery();
            if (!rs.next()) {
                return post;
            }
            post = new Post(BigInteger.valueOf(rs.getLong("id")),
                    rs.getString("postContains"),BigInteger.valueOf(rs.getLong("topicId")),
                    BigInteger.valueOf(rs.getLong("authorId")));
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(rs);
            MySQLUtil.close(findPostByIdStatement);
            MySQLUtil.close(connection);
        }
        return post;
    }

    @Override
    public void update(Post post) {
        if (post.getId() == null) {
            throw new IllegalArgumentException("Empty id field");
        }
        Object [] fields = {
                post.getPostContains(),
                post.getAuthorId(),
                post.getTopicId(),
                post.getId()
        };
        Connection connection = null;
        PreparedStatement createPostStatement = null;
        try {
            connection = connectionPool.getConnection();
            createPostStatement = MySQLUtil.prepareStatement(connection, UPDATE_QUERY, false, fields);
            createPostStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(createPostStatement);
            MySQLUtil.close(connection);
        }
    }

    @Override
    public void delete(BigInteger id) {
        Connection connection = null;
        PreparedStatement deletePostStatement = null;
        try {
            connection = connectionPool.getConnection();
            deletePostStatement = MySQLUtil.prepareStatement(connection, DELETE_QUERY, false, id);
            deletePostStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(deletePostStatement);
            MySQLUtil.close(connection);
        }
    }

    @Override
    public List<Post> findPostByTopicId(BigInteger topicId) {
        LinkedList<Post> foundPosts = new LinkedList<Post>();
        Connection connection = null;
        PreparedStatement findPostsByTopicIdStatement = null;
        ResultSet resultSet = null;
        try {
            connection = connectionPool.getConnection();
            findPostsByTopicIdStatement = MySQLUtil.prepareStatement(connection,
                    FIND_POSTS_BY_TOPIC_ID_QUERY,false,topicId);
            resultSet = findPostsByTopicIdStatement.executeQuery();
            while (resultSet.next()) {
                foundPosts.add(new Post(BigInteger.valueOf(resultSet.getLong("id")),
                        resultSet.getString("postContains"),
                        BigInteger.valueOf(resultSet.getLong("authorId")),
                        BigInteger.valueOf(resultSet.getLong("topicId"))));
            }
        } catch (SQLException e) {
            // TODO auto-generated try-catch block
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } finally {
            MySQLUtil.close(resultSet);
            MySQLUtil.close(findPostsByTopicIdStatement);
            MySQLUtil.close(connection);
        }
        return foundPosts;
    }
}
